package goodroommate.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import goodroommate.dal.DatabaseSession;
import goodroommate.dao.IUniversityDao;
import goodroommate.domain.University;
import goodroommate.domain.UniversityDetail;
import goodroommate.exception.DatabaseException;

public class UniversityDao implements IUniversityDao {

	@Override
	public boolean addUniversityDetail(UniversityDetail universitydetail, DatabaseSession dbs)
			throws DatabaseException {
		
		String sql="INSERT INTO universitydetails(UniversityID,Subject,Description";
		String sqlval="VALUES(?,?,?";
		ArrayList<Object> params=new ArrayList<Object>();
		params.add(universitydetail.getUniversityid());
		params.add(universitydetail.getSubject());
		params.add(universitydetail.getDescription());
		if(universitydetail.getProfilepicture()!=null){
			sql+=",ProfilePicture";
			sqlval+=",? ";
			params.add(universitydetail.getProfilepicture());
		}
		sql+=") ";
		sqlval+=")";
		sql+=sqlval;
		try{
			dbs.executeUpdate(sql,params);
			return true;
		}catch(DatabaseException e){
			throw new DatabaseException(e);
		}
	}

	@Override
	public UniversityDetail getUniversityDetailById(long universityid, DatabaseSession dbs) throws DatabaseException {
		
		if(universityid==0)
			return null;
		String sql="SELECT * FROM universitydetails WHERE UniversityID=?";
		try{
			ResultSet rs=dbs.executeQuery(sql,universityid);
			if(rs.next()){
				UniversityDetail universitydetail=new UniversityDetail();
				universitydetail.setUniversityid(rs.getInt("UniversityID"));
				universitydetail.setSubject(rs.getString("Subject"));
				universitydetail.setDescription(rs.getString("Description"));
				universitydetail.setProfilepicture(rs.getString("ProfilePicture"));
				return universitydetail;
			}
				return null;
		}catch(DatabaseException e){
			throw new DatabaseException();
		} catch (SQLException e) {
			throw new DatabaseException();
		}
	}

	@Override
	public boolean updateUniversityDetail(UniversityDetail universitydetail, DatabaseSession dbs)
			throws DatabaseException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public ArrayList<UniversityDetail> getAllUniversityDetail(DatabaseSession dbs) throws DatabaseException {
		String sql = "SELECT universitydetails.*,universities.Name FROM universitydetails,universities WHERE universities.ID=universitydetails.UniversityID";
		ArrayList<UniversityDetail>rst=new ArrayList<UniversityDetail>();
		try{
			ResultSet rs=dbs.executeQuery(sql);
			while(rs.next()){
				UniversityDetail universityDetail=new UniversityDetail();
				universityDetail.setUniversityid(rs.getLong("UniversityID"));
				universityDetail.setUniversityname(rs.getString("Name"));
				universityDetail.setSubject(rs.getString("Subject"));
				rst.add(universityDetail);		
			}
			return rst;
		} catch (DatabaseException e) {
			throw new DatabaseException();
		} catch (SQLException e) {
			throw new DatabaseException();
		}
	}

	@Override
	public University getUniversityById(long universityid, DatabaseSession dbs) throws DatabaseException {
		if (universityid == 0)
			return null;
		String sql = "SELECT * FROM universities WHERE ID=?";
		try {
			ResultSet rs = dbs.executeQuery(sql, universityid);
			if (rs.next()) {
				University university = new University();
				university.setId(rs.getLong("ID"));
				university.setName(rs.getString("Name"));
				university.setMinistry(rs.getString("Ministry"));
				university.setLocation(rs.getString("Location"));
				university.setLevel(rs.getString("Level"));
				university.setInfo(rs.getString("Info"));
				return university;
			}
			return null;
		} catch (DatabaseException e) {
			throw new DatabaseException();
		} catch (SQLException e) {
			throw new DatabaseException();
		}
	}

}
